from model.MysqlPool import pool
from tornado import gen


class Record:
    # ----------add----------
    @classmethod
    @gen.coroutine
    def add(cls, c_id_key, s_id, recog_num, status) -> bool:
        """
        添加记录
        :param c_id_key: 课程号,此选项具体到某天的某节课
        :param s_id: 学号
        :param recog_num: 脸的次数
        :param status: 出勤状态
        :return:
        """

        sql = "INSERT INTO record_info (c_id_key, s_id, recog_num, status) VALUES (%s,%s,%s, %s)"
        cursor = yield pool.execute(sql, (c_id_key, s_id, recog_num, status))
        return cursor.rowcount != 0

    # ---------update---------
    @classmethod
    @gen.coroutine
    def update_state(cls, r_id, status):
        """
        修改考勤状态
        :param r_id: 记录号
        :param status: 出勤状态 0出勤 1未到 2请假
        :return:
        """
        sql = "UPDATE record_info SET status=%s WHERE r_id= %s"
        cursor = yield pool.execute(sql, (status, r_id))
        return cursor.rowcount != 0

    # ----------get----------
    @classmethod
    @gen.coroutine
    def get_by_teacher(cls, t_id, c_id="", s_id="", date="", number=""):
        """
        根据课堂id和职工号获取某学生的出勤状态
        :param t_id: 职工号
        :param c_id: 课程号
        :param s_id: 学号
        :param date: 时间
        :param number: 节次
        :return:
        """
        sql = "select r_id as rid, s_name as name, s_id as id, t_name as tname, date_format(c_date, '%%Y-%%m-%%d') as date, c_number as number, c_name as cname, recog_num, status " \
              "from record where t_id= %s"
        parm = [t_id, ]
        if len(c_id) != 0:
            parm.append(c_id)
            sql = "{} and c_id= %s".format(sql)
        if len(s_id) != 0:
            parm.append(s_id)
            sql = "{} and s_id= %s".format(sql)
        if len(date) != 0:
            parm.append(date)
            sql = "{} and c_date= %s".format(sql)
        if len(number) != 0:
            parm.append(number)
            sql = "{} and c_number= %s".format(sql)
        cursor = yield pool.execute(sql, parm)
        if cursor.rowcount == 0:
            return ()
        else:
            return cursor.fetchall()

    @classmethod
    @gen.coroutine
    def get_by_teacher_summary(cls, t_id, c_id):
        """
        根据课堂课程号和职工号获取本教师期末汇总信息
        :param t_id:
        :param c_id:
        :return: ({name, id, attend_count, absent_count, qj_count},)
        """
        sql = "select s_name as name, s_id as id, count(CASE WHEN (status=0) THEN 1 END) as attend_count, count(CASE WHEN (status=1) THEN 1 END) as absent_count, count(CASE WHEN (status=2) THEN 1 END) as qj_count " \
              "from record where t_id=%s and c_id=%s group by s_id"
        cursor = yield pool.execute(sql, (t_id, c_id))
        if cursor.rowcount == 0:
            return ()
        else:
            return cursor.fetchall()

    @classmethod
    @gen.coroutine
    def get_by_student_class(cls, s_id, c_id) -> ():
        """
        根据课堂id和学号获取某堂课所有学生的出勤状态
        :param s_id: 学号
        :param c_id: 课程id
        :return:
        """
        sql = "select r_id as rid, s_name as name, s_id as id, t_name as tname, c_date as date, c_number as number,recog_num,status " \
              "from record where s_id=%s and c_id=%s"
        cursor = yield pool.execute(sql, (s_id, c_id))
        if cursor.rowcount == 0:
            return ()
        else:
            data = cursor.fetchall()
            for x in data:
                x["date"] = str(x["date"])
            return data

    # ----------del----------
    @classmethod
    @gen.coroutine
    def del_by_class_stu(cls, r_id) -> bool:
        """
            删除考勤信息 返回是否成功
            :param r_id: 记录号
            :return:
        """
        sql = "delete from record_info where r_id = %s"
        cursor = yield pool.execute(sql, (r_id, ))
        return cursor.rowcount != 0
